/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.OnlineHouseLeaseSystem.common;

import com.OnlineHouseLeaseSystem.database.DBManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author gas1121
 */
public class UserManager {
    private final static String USER_NAME = "user_name";
    private final static String PASSWORD = "password";
    private final static String USER_ID = "user_id";
    private final static String GENDER = "gender";
    private final static String PHONE_NUMBER = "phone_number";
    private final static String ID = "ID";
    private final static String USER_ADDRESS = "user_address";
    private final static String AUTHORITY = "authority";
    
    private final static String LOCK_TABLE = "LOCK TABLE house_information IN EXCLUSIVE MODE; ";

    private final static String GET_USER_INFORMATION = "SELECT * FROM user_information ORDER BY user_id DESC";
    private final static String GET_MAX_USER_ID = "SELECT MAX(user_id) FROM user_information ";
    private final static String INSERT_USER_HEAD = "INSERT INTO user_information VALUES ( '";
    private final static String INSERT_USER_FOOT = "' ) ";
    private final static String UPDATE_USER_INFORMATION = "UPDATE user_information SET ";
    private final static String DELETE_USER = "DELETE FROM user_information WHERE user_id='";

    public static User getUserInfomation(String user_name)
    {
        User user = new User();
        user.setUser_id(-1);
        user.setUser_name("");
        user.setPassword("");
        user.setAuthority(0);
        user.setGender("");
        user.setPhone_number("");
        user.setID("");
        user.setUser_address("");

        try
        {
            DBManager dbManager = new DBManager();
            ResultSet result = dbManager.executeQuery(GET_USER_INFORMATION);
            while(result.next())
            {
                if(result.getString(USER_NAME).equals(user_name))
                {
                    user.setUser_id(result.getInt(USER_ID));
                    user.setUser_name(result.getString(USER_NAME));
                    user.setPassword(result.getString(PASSWORD));
                    user.setAuthority(result.getInt(AUTHORITY));
                    user.setGender(result.getString(GENDER));
                    user.setPhone_number(result.getString(PHONE_NUMBER));
                    user.setID(result.getString(ID));
                    user.setUser_address(result.getString(USER_ADDRESS));
                    break;
                }
            }
            dbManager.close();
        }
        catch (SQLException ex)
        {
            Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return user;
    }
    
    public static User getUserInfomation(int user_id)
    {
        User user = new User();
        user.setUser_id(-1);
        user.setUser_name("");
        user.setPassword("");
        user.setAuthority(0);
        user.setGender("");
        user.setPhone_number("");
        user.setID("");
        user.setUser_address("");

        try
        {
            DBManager dbManager = new DBManager();
            ResultSet result = dbManager.executeQuery(GET_USER_INFORMATION);
            while(result.next())
            {
                if(result.getString(USER_NAME).equals(user_id))
                {
                    user.setUser_id(result.getInt(USER_ID));
                    user.setUser_name(result.getString(USER_NAME));
                    user.setPassword(result.getString(PASSWORD));
                    user.setAuthority(result.getInt(AUTHORITY));
                    user.setGender(result.getString(GENDER));
                    user.setPhone_number(result.getString(PHONE_NUMBER));
                    user.setID(result.getString(ID));
                    user.setUser_address(result.getString(USER_ADDRESS));
                    break;
                }
            }
            dbManager.close();
        }
        catch (SQLException ex)
        {
            Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return user;
    }
    
    public static boolean isUser_NameExisted (String user_name)
    {
    	 try
         {
             DBManager dbManager = new DBManager();
             ResultSet result = dbManager.executeQuery(GET_USER_INFORMATION);
             while(result.next())
             {
                 if(result.getString("user_name").equals(user_name))
                 {
                     dbManager.close();
                     return true;
                 }
             }
             dbManager.close();
         }
         catch (SQLException ex)
         {
             Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
         }
         return false;
    }

    public static int getUser_idForNew()
    {
        int maxUid = 0;
        try
        {
            DBManager dbManager = new DBManager();
            ResultSet result = dbManager.executeQuery(LOCK_TABLE+GET_MAX_USER_ID);
            result.next();
            maxUid = result.getInt(1);
            dbManager.close();
        }
        catch (SQLException ex)
        {
            Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return maxUid+1;
    }

    public static void insertNewUser(String user_name, String password,
            String gender, String phone_number, String ID, String user_address)
    {
        int user_id = getUser_idForNew();
        try
        {
            DBManager dbManager = new DBManager();
            dbManager.executeUpdate(INSERT_USER_HEAD +
                    user_id + "','" + user_name + "','" + password + "','" +
                    "0" + "','" + gender + "','" + phone_number + "','" + ID + "','" +
                    user_address + INSERT_USER_FOOT);
            dbManager.close();
        }
        catch (SQLException ex)
        {
            Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public static void updateUserInformation(String user_name, String password, String gender,
    		String phone_number, String ID, String user_address)
    {
    	try
    	{
    		DBManager dbManager = new DBManager();
    		dbManager.executeUpdate(UPDATE_USER_INFORMATION + "password='" + password + "', gender='"
    				+ gender + "', phone_number='" + phone_number + "', ID='" + ID + "', user_address='"
    				+ user_address + "' WHERE user_name='" + user_name + "'");
    		dbManager.close();
    	}
    	catch (SQLException ex)
    	{
    		Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
    	}
    }
    
    public static ArrayList getALLUser()
    {
    	ArrayList list = new ArrayList();
    	try
    	{
    		DBManager dbManager = new DBManager();
    		ResultSet result = dbManager.executeQuery(GET_USER_INFORMATION);
    		while(result.next())
    		{
    			User user = new User();
    			user.setUser_id(result.getInt(USER_ID));
                user.setUser_name(result.getString(USER_NAME));
                user.setPassword(result.getString(PASSWORD));
                user.setAuthority(result.getInt(AUTHORITY));
                user.setGender(result.getString(GENDER));
                user.setPhone_number(result.getString(PHONE_NUMBER));
                user.setID(result.getString(ID));
                user.setUser_address(result.getString(USER_ADDRESS));
                list.add(user);
    		}
    		dbManager.close();
    	}
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return list;
    }
    
    public static void deleteUser (int user_id)
    {
    	try
    	{
    		DBManager dbManager = new DBManager();
    		dbManager.executeUpdate(DELETE_USER + user_id + "'");
    		dbManager.close();
    	}
    	catch (SQLException ex)
    	{
    		Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
    	}
    }
}
